<!DOCTYPE html>
<html prefix="og: http://ogp.me/ns# article: http://ogp.me/ns/article# " lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Postgres Import CSV cp936 Error | 绿萝间</title>
<link href="../assets/css/all-nocdn.css" rel="stylesheet" type="text/css">
<link href="../assets/css/ipython.min.css" rel="stylesheet" type="text/css">
<link href="../assets/css/nikola_ipython.css" rel="stylesheet" type="text/css">
<meta name="theme-color" content="#5670d4">
<meta name="generator" content="Nikola (getnikola.com)">
<link rel="alternate" type="application/rss+xml" title="RSS" href="../rss.xml">
<link rel="canonical" href="https://muxuezi.github.io/posts/postgres-import-csv-cp936-error.html">
<script type="text/x-mathjax-config">
MathJax.Hub.Config({
    tex2jax: {
        inlineMath: [ ['$','$'], ["\\(","\\)"] ],
        displayMath: [ ['$$','$$'], ["\\[","\\]"] ],
        processEscapes: true
    },
    displayAlign: 'center', // Change this to 'center' to center equations.
    "HTML-CSS": {
        styles: {'.MathJax_Display': {"margin": 0}}
    }
});
</script><!--[if lt IE 9]><script src="../assets/js/html5.js"></script><![endif]--><meta name="author" content="Tao Junjie">
<link rel="prev" href="dlott-15145-2015-12-12-report.html" title="大乐透15145期(2015-12-12)数据分析报告" type="text/html">
<link rel="next" href="sqlalchemy-introduce.html" title="SQLAlchemy Introduce" type="text/html">
<meta property="og:site_name" content="绿萝间">
<meta property="og:title" content="Postgres Import CSV cp936 Error">
<meta property="og:url" content="https://muxuezi.github.io/posts/postgres-import-csv-cp936-error.html">
<meta property="og:description" content="Postgres导入CSV中文编码错误¶现在公司数据资源非常丰富，动辄要处理的csv就1E8行纪录，10G，分析起来相当爽。编码格式是cp936，用官方的COPY命令导入Postgres，一直出错，目前COPY的ENCODING不支持cp936。试着用Pandas读几行，保持csv为utf-8再COPY没问题。
解决办法：通过Python3的Pandas读取CSV转换成utf－8，再利用sqlal">
<meta property="og:type" content="article">
<meta property="article:published_time" content="2015-12-13T19:11:05+08:00">
<meta property="article:tag" content="ipython">
<meta property="article:tag" content="sql">
</head>
<body>
<a href="#content" class="sr-only sr-only-focusable">Skip to main content</a>

<!-- Menubar -->

<nav class="navbar navbar-inverse navbar-static-top"><div class="container">
<!-- This keeps the margins nice -->
        <div class="navbar-header">
            <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-navbar" aria-controls="bs-navbar" aria-expanded="false">
            <span class="sr-only">Toggle navigation</span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            </button>
            <a class="navbar-brand" href="https://muxuezi.github.io/">

                <span id="blog-title">绿萝间</span>
            </a>
        </div>
<!-- /.navbar-header -->
        <div class="collapse navbar-collapse" id="bs-navbar" aria-expanded="false">
            <ul class="nav navbar-nav">
<li>
<a href="../archive.html">Archive</a>
                </li>
<li>
<a href="../categories/">Tags</a>
                </li>
<li>
<a href="../rss.xml">RSS feed</a>

                
            </li>
</ul>
<ul class="nav navbar-nav navbar-right">
<li>
    <a href="postgres-import-csv-cp936-error.ipynb" id="sourcelink">Source</a>
    </li>

                
            </ul>
</div>
<!-- /.navbar-collapse -->
    </div>
<!-- /.container -->
</nav><!-- End of Menubar --><div class="container" id="content" role="main">
    <div class="body-content">
        <!--Body content-->
        <div class="row">
            
            
<article class="post-text h-entry hentry postpage" itemscope="itemscope" itemtype="http://schema.org/Article"><header><h1 class="p-name entry-title" itemprop="headline name"><a href="#" class="u-url">Postgres Import CSV cp936 Error</a></h1>

        <div class="metadata">
            <p class="byline author vcard"><span class="byline-name fn">
                    Tao Junjie
            </span></p>
            <p class="dateline"><a href="#" rel="bookmark"><time class="published dt-published" datetime="2015-12-13T19:11:05+08:00" itemprop="datePublished" title="2015-12-13 19:11">2015-12-13 19:11</time></a></p>
            
        <p class="sourceline"><a href="postgres-import-csv-cp936-error.ipynb" id="sourcelink">Source</a></p>

        </div>
        

    </header><div class="e-content entry-content" itemprop="articleBody text">
    <div tabindex="-1" id="notebook" class="border-box-sizing">
    <div class="container" id="notebook-container">

<div class="cell border-box-sizing text_cell rendered">
<div class="prompt input_prompt">
</div>
<div class="inner_cell">
<div class="text_cell_render border-box-sizing rendered_html">
<h2 id="Postgres导入CSV中文编码错误">Postgres导入CSV中文编码错误<a class="anchor-link" href="postgres-import-csv-cp936-error.html#Postgres%E5%AF%BC%E5%85%A5CSV%E4%B8%AD%E6%96%87%E7%BC%96%E7%A0%81%E9%94%99%E8%AF%AF">¶</a>
</h2>
<p>现在公司数据资源非常丰富，动辄要处理的csv就1E8行纪录，10G，分析起来相当爽。编码格式是cp936，用官方的COPY命令导入Postgres，一直出错，目前COPY的ENCODING不支持cp936。试着用Pandas读几行，保持csv为utf-8再COPY没问题。</p>
<p>解决办法：通过Python3的Pandas读取CSV转换成utf－8，再利用sqlalchemy＋psycopg2（目前只支持到Python3.4，还不支持3.5）导入即可。</p>
<blockquote>
<p>这么多依赖推荐使用<a href="https://www.continuum.io/downloads">Anaconda</a>，十分方便，尤其适合解决在Windows平台编译C语言相关库（lxml，psycopg2，numpy，pandas）时的各种奇葩问题。</p>
</blockquote>
<p>具体方法如下：</p>
<!-- TEASER_END-->
</div>
</div>
</div>
<div class="cell border-box-sizing code_cell rendered">
<div class="input">
<div class="prompt input_prompt">In [1]:</div>
<div class="inner_cell">
    <div class="input_area">
<div class=" highlight hl-ipython3"><pre><span></span><span class="kn">import</span> <span class="nn">pandas</span> <span class="k">as</span> <span class="nn">pd</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">create_engine</span>
<span class="kn">import</span> <span class="nn">ipython_memory_usage.ipython_memory_usage</span> <span class="k">as</span> <span class="nn">imu</span>
</pre></div>

</div>
</div>
</div>

</div>
<div class="cell border-box-sizing code_cell rendered">
<div class="input">
<div class="prompt input_prompt">In [2]:</div>
<div class="inner_cell">
    <div class="input_area">
<div class=" highlight hl-ipython3"><pre><span></span><span class="n">imu</span><span class="o">.</span><span class="n">start_watching_memory</span><span class="p">()</span>
</pre></div>

</div>
</div>
</div>

<div class="output_wrapper">
<div class="output">


<div class="output_area">
<div class="prompt"></div>
<div class="output_subarea output_stream output_stdout output_text">
<pre>In [2] used 0.0195 MiB RAM in 0.12s, peaked 0.00 MiB above current, total RAM usage 61.92 MiB
</pre>
</div>
</div>

</div>
</div>

</div>
<div class="cell border-box-sizing code_cell rendered">
<div class="input">
<div class="prompt input_prompt">In [3]:</div>
<div class="inner_cell">
    <div class="input_area">
<div class=" highlight hl-ipython3"><pre><span></span><span class="n">chunksize</span> <span class="o">=</span> <span class="mf">1E6</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s1">'postgresql+psycopg2://postgres:tianKong7@localhost:5432/postgres'</span><span class="p">)</span>
</pre></div>

</div>
</div>
</div>

<div class="output_wrapper">
<div class="output">


<div class="output_area">
<div class="prompt"></div>
<div class="output_subarea output_stream output_stdout output_text">
<pre>In [3] used 1.3125 MiB RAM in 0.16s, peaked 0.00 MiB above current, total RAM usage 63.23 MiB
</pre>
</div>
</div>

</div>
</div>

</div>
<div class="cell border-box-sizing code_cell rendered">
<div class="input">
<div class="prompt input_prompt">In [ ]:</div>
<div class="inner_cell">
    <div class="input_area">
<div class=" highlight hl-ipython3"><pre><span></span><span class="n">df</span> <span class="o">=</span> <span class="n">pd</span><span class="o">.</span><span class="n">read_csv</span><span class="p">(</span><span class="s1">'c_全年库存结构变动_20151124122730.csv'</span><span class="p">,</span> <span class="n">encoding</span><span class="o">=</span><span class="s1">'cp936'</span><span class="p">,</span> <span class="n">chunksize</span><span class="o">=</span><span class="n">chunksize</span><span class="p">)</span>
</pre></div>

</div>
</div>
</div>

<div class="output_wrapper">
<div class="output">


<div class="output_area">
<div class="prompt"></div>
<div class="output_subarea output_stream output_stdout output_text">
<pre>In [4] used 4.0273 MiB RAM in 0.12s, peaked 0.00 MiB above current, total RAM usage 67.26 MiB
</pre>
</div>
</div>

</div>
</div>

</div>
<div class="cell border-box-sizing code_cell rendered">
<div class="input">
<div class="prompt input_prompt">In [ ]:</div>
<div class="inner_cell">
    <div class="input_area">
<div class=" highlight hl-ipython3"><pre><span></span><span class="k">for</span> <span class="n">chunk</span> <span class="ow">in</span> <span class="n">df</span><span class="p">:</span>
    <span class="n">chunk</span><span class="o">.</span><span class="n">to_sql</span><span class="p">(</span><span class="s2">"inventory"</span><span class="p">,</span> <span class="n">engine</span><span class="p">,</span> <span class="n">if_exists</span><span class="o">=</span><span class="s1">'append'</span><span class="p">,</span> <span class="n">chunksize</span><span class="o">=</span><span class="n">chunksize</span><span class="p">)</span>
    <span class="k">del</span> <span class="n">chunk</span>
</pre></div>

</div>
</div>
</div>

</div>
<div class="cell border-box-sizing code_cell rendered">
<div class="input">
<div class="prompt input_prompt">In [ ]:</div>
<div class="inner_cell">
    <div class="input_area">
<div class=" highlight hl-ipython3"><pre><span></span><span class="c1"># CREATE TABLE inv (</span>
<span class="c1">#     stat_date	date,</span>
<span class="c1">#     store_src_cd	int,</span>
<span class="c1">#     sku_id	int,</span>
<span class="c1">#     stock_qty	int,</span>
<span class="c1">#     canuse_qty	int,</span>
<span class="c1">#     len	int,</span>
<span class="c1">#     width	int,</span>
<span class="c1">#     height	int,</span>
<span class="c1">#     item_first_cate_name	varchar(80),</span>
<span class="c1">#     item_second_cate_name	varchar(80),</span>
<span class="c1">#     item_third_cate_name	varchar(80)</span>
<span class="c1"># );</span>

<span class="c1"># COPY inv FROM 'D:\\reader\\dask-tutorial\\top200.csv' DELIMITER ',' CSV;</span>
</pre></div>

</div>
</div>
</div>

</div>
    </div>
  </div>

    </div>
    <aside class="postpromonav"><nav><ul itemprop="keywords" class="tags">
<li><a class="tag p-category" href="../categories/ipython.html" rel="tag">ipython</a></li>
            <li><a class="tag p-category" href="../categories/sql.html" rel="tag">sql</a></li>
        </ul>
<ul class="pager hidden-print">
<li class="previous">
                <a href="dlott-15145-2015-12-12-report.html" rel="prev" title="大乐透15145期(2015-12-12)数据分析报告">Previous post</a>
            </li>
            <li class="next">
                <a href="sqlalchemy-introduce.html" rel="next" title="SQLAlchemy Introduce">Next post</a>
            </li>
        </ul></nav></aside><script type="text/javascript" src="https://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"> </script><script type="text/x-mathjax-config">
MathJax.Hub.Config({
    tex2jax: {
        inlineMath: [ ['$','$'], ["\\(","\\)"] ],
        displayMath: [ ['$$','$$'], ["\\[","\\]"] ],
        processEscapes: true
    },
    displayAlign: 'center', // Change this to 'center' to center equations.
    "HTML-CSS": {
        styles: {'.MathJax_Display': {"margin": 0}}
    }
});
</script></article>
</div>
        <!--End of body content-->

        <footer id="footer">
            Contents © 2017         <a href="mailto:muxuezi@gmail.com">Tao Junjie</a> - Powered by         <a href="https://getnikola.com" rel="nofollow">Nikola</a>         
<a rel="license" href="http://creativecommons.org/licenses/by-nc-sa/4.0">
<img alt="Creative Commons License BY-NC-SA" style="border-width:0; margin-bottom:12px;" src="http://i.creativecommons.org/l/by-nc-sa/4.0/80x15.png"></a>
            
        </footer>
</div>
</div>


            <script src="../assets/js/all-nocdn.js"></script><script>$('a.image-reference:not(.islink) img:not(.islink)').parent().colorbox({rel:"gal",maxWidth:"100%",maxHeight:"100%",scalePhotos:true});</script><!-- fancy dates --><script>
    moment.locale("en");
    fancydates(0, "YYYY-MM-DD HH:mm");
    </script><!-- end fancy dates --><script>
  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
  })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

  ga('create', 'UA-51330059-1', 'auto');
  ga('send', 'pageview');

</script>
</body>
</html>
